<?php

	require('../database/bd_connect.php');


if (isset($_GET['query'])){	
	$query = $_GET['query'] ;

	//QUERY ADICIONA +1 AOS VODAFONES

	//QUERY PARA OS MINUTOS
	if($query==1){
		$sql = "SELECT cpe.city AS City, count(distinct wifi_data.mac , if(wifi_data.ssid LIKE 'VODA%', wifi_data.ssid, NULL))+count(distinct cpe.mac, if(cpe.ssid LIKE 'VODA%', cpe.ssid, NULL)) AS Vodafone, count(distinct wifi_data.mac, if(wifi_data.ssid LIKE 'MEO%' and wifi_data.ssid NOT LIKE 'MEO-WIFI', wifi_data.ssid, NULL))+count(distinct cpe.mac, if(cpe.ssid LIKE 'MEO%', cpe.ssid, NULL)) AS MEO, count(distinct wifi_data.mac, if(wifi_data.ssid LIKE 'NOS%' OR wifi_data.ssid LIKE 'ZON%' AND wifi_data.ssid NOT LIKE 'FON_%' , wifi_data.ssid, NULL))+count(distinct cpe.mac, if(cpe.ssid LIKE 'ZON%', cpe.ssid, NULL)) AS ZON, (count(distinct wifi_data.mac, wifi_data.ssid)-count(distinct wifi_data.mac, if(wifi_data.ssid LIKE 'VODA%', wifi_data.ssid, NULL))-count(distinct wifi_data.mac, if(wifi_data.ssid LIKE 'MEO%' OR wifi_data.ssid LIKE 'MEO-WIFI', wifi_data.ssid, NULL))- count(distinct wifi_data.mac, if(wifi_data.ssid LIKE 'ZON%' AND wifi_data.ssid LIKE 'FON_%', wifi_data.ssid, NULL)))+count(distinct cpe.mac, if(cpe.ssid NOT LIKE 'MEO%' and cpe.ssid NOT LIKE 'VODA%' and cpe.ssid NOT LIKE 'ZON%', cpe.ssid, NULL)) AS Others FROM wifi_data, cpe where wifi_data.cpe_id = cpe.cpe_id AND time BETWEEN DATE_SUB(NOW(), INTERVAL 10 MINUTE) AND DATE_ADD(NOW(), INTERVAL 0 MINUTE) GROUP BY cpe.city ; ";
	}
	else if($query==2){
		//QUERY PARA OS DIAS
		$sql = "SELECT cpe.city AS City, count(distinct wifi_hour.mac , if(wifi_hour.ssid LIKE 'VODA%', wifi_hour.ssid, NULL))+count(distinct cpe.mac, if(cpe.ssid LIKE 'VODA%', cpe.ssid, NULL)) AS Vodafone, count(distinct wifi_hour.mac, if(wifi_hour.ssid LIKE 'MEO%' and wifi_hour.ssid NOT LIKE 'MEO-WIFI', wifi_hour.ssid, NULL))+count(distinct cpe.mac, if(cpe.ssid LIKE 'MEO%', cpe.ssid, NULL)) AS MEO, count(distinct wifi_hour.mac, if(wifi_hour.ssid LIKE 'NOS%' OR wifi_hour.ssid LIKE 'ZON%' AND wifi_hour.ssid NOT LIKE 'FON_%' , wifi_hour.ssid, NULL))+count(distinct cpe.mac, if(cpe.ssid LIKE 'ZON%', cpe.ssid, NULL)) AS ZON, (count(distinct wifi_hour.mac, wifi_hour.ssid)-count(distinct wifi_hour.mac, if(wifi_hour.ssid LIKE 'VODA%', wifi_hour.ssid, NULL))-count(distinct wifi_hour.mac, if(wifi_hour.ssid LIKE 'MEO%' OR wifi_hour.ssid LIKE 'MEO-WIFI', wifi_hour.ssid, NULL))- count(distinct wifi_hour.mac, if(wifi_hour.ssid LIKE 'ZON%' AND wifi_hour.ssid LIKE 'FON_%', wifi_hour.ssid, NULL)))+count(distinct cpe.mac, if(cpe.ssid NOT LIKE 'MEO%' and cpe.ssid NOT LIKE 'VODA%' and cpe.ssid NOT LIKE 'ZON%', cpe.ssid, NULL)) AS Others FROM wifi_hour, cpe where wifi_hour.cpe_id = cpe.cpe_id AND time >= DATE_SUB(CURDATE(), INTERVAL 6 DAY) GROUP BY cpe.city ;";
	}
	else if($query==3){
		//QUERY PARA AS SEMANAS
		$sql = "SELECT cpe.city AS City, count(distinct wifi_hour.mac , if(wifi_hour.ssid LIKE 'VODA%', wifi_hour.ssid, NULL))+count(distinct cpe.mac, if(cpe.ssid LIKE 'VODA%', cpe.ssid, NULL)) AS Vodafone, count(distinct wifi_hour.mac, if(wifi_hour.ssid LIKE 'MEO%' and wifi_hour.ssid NOT LIKE 'MEO-WIFI', wifi_hour.ssid, NULL))+count(distinct cpe.mac, if(cpe.ssid LIKE 'MEO%', cpe.ssid, NULL)) AS MEO, count(distinct wifi_hour.mac, if(wifi_hour.ssid LIKE 'NOS%' OR wifi_hour.ssid LIKE 'ZON%' AND wifi_hour.ssid NOT LIKE 'FON_%' , wifi_hour.ssid, NULL))+count(distinct cpe.mac, if(cpe.ssid LIKE 'ZON%', cpe.ssid, NULL)) AS ZON, (count(distinct wifi_hour.mac, wifi_hour.ssid)-count(distinct wifi_hour.mac, if(wifi_hour.ssid LIKE 'VODA%', wifi_hour.ssid, NULL))-count(distinct wifi_hour.mac, if(wifi_hour.ssid LIKE 'MEO%' OR wifi_hour.ssid LIKE 'MEO-WIFI', wifi_hour.ssid, NULL))- count(distinct wifi_hour.mac, if(wifi_hour.ssid LIKE 'ZON%' AND wifi_hour.ssid LIKE 'FON_%', wifi_hour.ssid, NULL)))+count(distinct cpe.mac, if(cpe.ssid NOT LIKE 'MEO%' and cpe.ssid NOT LIKE 'VODA%' and cpe.ssid NOT LIKE 'ZON%', cpe.ssid, NULL)) AS Others FROM wifi_hour, cpe where wifi_hour.cpe_id = cpe.cpe_id AND time >= DATE_SUB(CURDATE(), INTERVAL 3 WEEK) GROUP BY cpe.city ;";

	}

	$result = $conn->query($sql);
	$num_cpe = mysqli_num_rows($result);

	while ($row = mysqli_fetch_array($result)) {
			  $city[] = iconv('windows-1252', 'utf-8', $row['City']);
			  $voda[] = $row['Vodafone'];
			  $meo[] = $row['MEO'];
			  $zon[] = $row['ZON'];
			  $oth[] = $row['Others'];
	}

	$output = array($city,$voda,$meo,$zon,$oth);

	echo json_encode($output);
}
else {
	echo "erro";
}
mysqli_close($conn);
?>
